package org.example.quartz.service.impl;

import cn.hutool.extra.spring.SpringUtil;
import org.example.quartz.service.SqlService;
import org.example.quartz.service.UtilService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

@Service
public class SqlServiceImpl implements SqlService {
    @Value(value = "${spring.datasource.oracleBip}")
    private String oracleBIP;
    @Value(value = "${spring.datasource.oracleBipBack}")
    private String oracleBipBack;

    @Value(value = "${spring.datasource.FreeMapHZ}")
    private String FreeMapHZ;
    UtilService utilService = SpringUtil.getBean(UtilService.class);

    /**
     * 行政部门
     *
     * @param ts
     * @param orgStr
     * @return
     */
    @Override
    public String getXZDeptSelectSql(String ts, String orgStr) {
        String selectSQL = "SELECT \n" +
                "DEPT.PK_DEPT AS YYID,\n" +
                "DEPT.CODE AS CODE,\n" +
                "DEPT.NAME,\n" +
                "(CASE WHEN FADEPT.code IS NULL THEN ORG.CODE ELSE FADEPT.CODE END) as PARENTCODE,\n" +
                "ORG.CODE AS ORGCODE,\n" +
                "'Department' as ORGTYPE,\n" +
                "(CASE WHEN FADEPT.code IS NULL THEN '是' ELSE '否' END) as ISORG,\n" +
                "DEPT.TEL AS TEL,\n" +
                "DEPT.TS AS TS\n" +
                "FROM \n" +
                " " + oracleBIP + ".ORG_DEPT" + utilService.getHZ() + "DEPT\n" +
                "LEFT JOIN " + oracleBIP + ".ORG_DEPT" + utilService.getHZ() + " FADEPT ON DEPT.pk_fatherorg = FADEPT.pk_dept\n" +
                "LEFT JOIN " + oracleBIP + ".ORG_ORGS" + utilService.getHZ() + " ORG ON DEPT.pk_org =ORG.PK_ORG \n" +
                " WHERE \n" +
                "DEPT.DR =0\n" +
                "AND\n" +
                "DEPT.ENABLESTATE =2\n" +
                "and \n" +
                "ORG.code in (" + orgStr + ")\n" +
                "AND\n" +
                "DEPT.TS > '" + ts + "'\n" +
                "ORDER BY LENGTH(DEPT.CODE) ASC ,DEPT.TS ASC\n";
        return selectSQL;
    }

    /**
     * 行政人员
     *
     * @param ts
     * @param orgStr
     * @return
     */
    @Override
    public String getXZEmployeeSelectSql(String ts, String orgStr) {
        String sql = "SELECT\n" +
                "PSN.CODE,\n" +
                "PSN.NAME,\n" +
                "ORG.CODE AS ORGCODE,\n" +
                "PSN.MOBILE AS CONTACTINFO,\n" +
                "PSN.TS AS TS,\n" +
                "DEPT.CODE AS DEPTCODE,\n" +
                "DEPT.NAME AS DEPTNAME,\n" +
                "'' AS EMAIL\n" +
                "FROM\n" + " " + oracleBIP + ".BD_PSNDOC" + utilService.getHZ() + " PSN\n" +
                "LEFT JOIN \n" +
                " " + oracleBIP + ".ORG_ORGS" + utilService.getHZ() + " ORG ON PSN.PK_ORG = ORG.PK_ORG\n" +
                "LEFT JOIN \n" +
                "(SELECT DISTINCT PK_PSNDOC,PK_DEPT FROM  " + oracleBIP + ".BD_PSNJOB" + utilService.getHZ() + " WHERE ISMAINJOB = 'Y' AND enddutydate is null) JOB ON PSN.PK_PSNDOC = JOB.PK_PSNDOC\n" +
                "LEFT JOIN \n" +
                " " + oracleBIP + ".ORG_DEPT" + utilService.getHZ() + " DEPT ON JOB.PK_DEPT = DEPT.PK_DEPT\n" + " WHERE \n" +
                "PSN.DR = 0\n" +
                "AND\n" +
                "PSN.ENABLESTATE =2\n" +
                "AND\n" +
                "PSN.TS > '" + ts + "'\n" +
                "AND\n" +
                "ORG.CODE IN (" + orgStr + ")\n" +
                "ORDER BY\n" +
                "PSN.TS ASC";
        return sql;
    }

    /**
     * 核算部门
     *
     * @param ts
     * @param orgStr
     * @return
     */
    @Override
    public String getHSDeptSelectSql(String ts, String orgStr) {
        String selectSQL = "SELECT \n" +
                "DEPT.PK_DEPT AS YYID,\n" +
                "DEPT.CODE AS CODE,\n" +
                "DEPT.NAME,\n" +
                "(CASE WHEN FADEPT.code IS NULL THEN ORG.CODE ELSE FADEPT.CODE END) as PARENTCODE,\n" +
                "ORG.CODE AS ORGCODE,\n" +
                "'Department' as ORGTYPE,\n" +
                "(CASE WHEN FADEPT.code IS NULL THEN '是' ELSE '否' END) as ISORG,\n" +
                "DEPT.TEL AS TEL,\n" +
                "DEPT.TS AS TS\n" +
                "FROM \n" +
                " " + oracleBIP + ".ORG_DEPT " + utilService.getHZ() + "DEPT\n" +
                "LEFT JOIN " + oracleBIP + ".ORG_DEPT" + utilService.getHZ() + " FADEPT ON DEPT.pk_fatherorg = FADEPT.pk_dept\n" +
                "LEFT JOIN " + oracleBIP + ".ORG_ORGS" + utilService.getHZ() + " ORG ON DEPT.pk_org =ORG.PK_ORG \n" +
                " WHERE \n" +
                "DEPT.DR =0\n" +
                "AND\n" +
                "DEPT.ENABLESTATE =2\n" +
                "and \n" +
                "ORG.code in (" + orgStr + ")\n" +
                "AND\n" +
                "DEPT.TS > '" + ts + "'\n" +
                "ORDER BY LENGTH(DEPT.CODE) ASC ,DEPT.TS ASC\n";
        return selectSQL;
    }

    /**
     * 核算人员
     *
     * @param ts
     * @param orgStr
     * @return
     */
    @Override
    public String getHSEmployeeSelectSql(String ts, String orgStr) {
        String sql = "SELECT\n" +
                "PSN.CODE,\n" +
                "PSN.NAME,\n" +
                "ORG.CODE AS ORGCODE,\n" +
                "PSN.MOBILE AS CONTACTINFO,\n" +
                "PSN.TS AS TS,\n" +
                "DEPT.CODE AS DEPTCODE,\n" +
                "DEPT.NAME AS DEPTNAME,\n" +
                "'' AS EMAIL\n" +
                "FROM\n" + " " + oracleBIP + ".BD_PSNDOC" + utilService.getHZ() + " PSN\n" +
                "LEFT JOIN \n" +
                " " + oracleBIP + ".ORG_ORGS" + utilService.getHZ() + " ORG ON PSN.PK_ORG = ORG.PK_ORG\n" +
                "LEFT JOIN \n" +
                "(SELECT DISTINCT PK_PSNDOC,PK_DEPT FROM  " + oracleBIP + ".BD_PSNJOB" + utilService.getHZ() + " WHERE ISMAINJOB = 'Y' AND enddutydate is null) JOB ON PSN.PK_PSNDOC = JOB.PK_PSNDOC\n" +
                "LEFT JOIN \n" +
                " " + oracleBIP + ".ORG_DEPT" + utilService.getHZ() + " DEPT ON JOB.PK_DEPT = DEPT.PK_DEPT\n" + " WHERE \n" +
                "PSN.DR = 0\n" +
                "AND\n" +
                "PSN.ENABLESTATE =2\n" +
                "AND\n" +
                "PSN.TS > '" + ts + "'\n" +
                "AND\n" +
                "ORG.CODE IN (" + orgStr + ")\n" +
                "ORDER BY\n" +
                "PSN.TS ASC";
        return sql;
    }

    /**
     * 往来单位
     *
     * @param ts
     * @return
     */
    @Override
    public String getCustomerSelectSql(String ts) {
        String sql = "SELECT--判断编码  客户01、供应商02、其他03\n" +
                "'03$'||CS.PK_CUST_SUP||CS.CODE  AS CODE,\n" +
                "CS.PK_CUST_SUP||CS.CODE||CS.NAME AS NAME,\n" +
                "CS.CODE AS EXTC_YCODE,\n" +
                "CS.NAME AS EXTC_YNAME,\n" +
                "--需按GSCloud系统往来单位类别定义中编号传递（后续提供）\n" +
                "'WLDWLB001' AS TYPECODE,\n" +
                "'集团内' AS TYPENAME,\n" +
                "--需按GSCloud系统往来单位地区定义中编号传递  往来单位地区编号0001\n" +
                "'' AS AREACODE,\n" +
                "--地区名称\n" +
                "'' AS AREANAME,\n" +
                "--往来单位公私类型\n" +
                "'1' AS DOMAINTYPE,\n" +
                "--ORGCODE\n" +
                "O.CODE AS ORGCODE,\n" +
                "--默认交易币种编号\n" +
                "'CNY' AS DEFAULTCURRENCY,\n" +
                " taxpayerid AS ORGANIZATIONCODE,\n" +
                "CS.PK_CUST_SUP AS ID," +
                "--单位性质编号\n" +
                "'' AS UNITNATURECODE,\n" +
                "--单位性质名称\n" +
                "'' AS UNITNATURENAME,\n" +
                "--单位类型编号\n" +
                "'' AS EXTC_TYPECODE,\n" +
                "--单位类型名称\n" +
                "'' AS EXTC_TYPENAME,\n" +
                "CS.TS AS TS \n" +
                "FROM\n" +
                " " + oracleBIP + ".BD_CUST_SUPPLIER" + utilService.getHZ() + " CS\n" +
                "LEFT JOIN " + oracleBIP + ".ORG_ORGS" + utilService.getHZ() + " O ON CS.PK_ORG = O.PK_ORG\n" +
                " where\n" +
                "CS.DR = 0\n" +
                "AND \n" +
                "CS.TS > '" + ts + "'" +
                " ORDER BY TS ASC";
        return sql;
    }

    /**
     * 往来单位-查询客户地址信息
     *
     * @param PK_CUST_SUP
     * @return
     */
    @Override
    public String getAddressSQL(String PK_CUST_SUP) {
        String sql = "--客户收货地址\n" +
                "select\n" +
                "'' AS CODE,\n" +
                "'' AS COUNTRYORREGION,\n" +
                "'' AS ADDRTYPE,\n" +
                "'' AS ISMAIN,\n" +
                "'' AS STREETNO,\n" +
                "'' AS CONTACTNUM,\n" +
                "'' AS REMARK\n" +
                "from\n" +
                "" + oracleBIP + ".bd_custaddress" + utilService.getHZ() + " \n" +
                "where\n" +
                "PK_CUSTOMER = '" + PK_CUST_SUP + "'\n" +
                "UNION\n" +
                "--供应商发货地址\n" +
                "select\n" +
                "'' AS CODE,\n" +
                "'' AS COUNTRYORREGION,\n" +
                "'' AS ADDRTYPE,\n" +
                "'' AS ISMAIN,\n" +
                "'' AS STREETNO,\n" +
                "'' AS CONTACTNUM,\n" +
                "'' AS REMARK \n" +
                "from \n" +
                "" + oracleBIP + ".bd_supaddress" + utilService.getHZ() + " \n" +
                "where \n" +
                "PK_SUPPLIER = '" + PK_CUST_SUP + "'";

        return sql;
    }

    /**
     * 往来单位-查询客户银行信息
     *
     * @param PK_CUST_SUP
     * @return
     */
    @Override
    public String getBankaccountSQL(String PK_CUST_SUP) {
        String sql = "--客户银行账号\n" +
                "SELECT\n" +
                "'' AS ACCOUNTCODE,\n" +
                "'' AS ACCOUNTNAME,\n" +
                "'' AS INBANK,\n" +
                "'' AS ACCOUNTSTATE,\n" +
                "'' AS PRIVATEORPUBLIC,\n" +
                "'' AS CURRENCYLIST,\n" +
                "'' AS ISMAIN,\n" +
                "'' AS COUNTRY,\n" +
                "'' AS PROVINCE,\n" +
                "'' AS CITY,\n" +
                "'' AS REMARK\n" +
                "FROM\n" + " " + oracleBIP + ".BD_CUSTBANK" + utilService.getHZ() + "\n" +
                "WHERE \n" +
                "PK_CUST = '" + PK_CUST_SUP + "'";
        return sql;
    }

    /**
     * 往来单位-查询客户联系人信息
     *
     * @param PK_CUST_SUP
     * @return
     */
    @Override
    public String getContactSQL(String PK_CUST_SUP) {
        String hz1 = " " + oracleBIP + ".BD_CUSTLINKMAN\n";
        String hz2 = " " + oracleBIP + ".BD_SUPLINKMAN\n";
        if (!ObjectUtils.isEmpty(oracleBipBack)) {
            hz1 = " " + oracleBIP + ".BD_CUSTLINKMAN" + "@" + oracleBipBack + "\n";
            hz2 = " " + oracleBIP + ".BD_SUPLINKMAN" + "@" + oracleBipBack + "\n";
        }
        String sql = "--客户联系人\n" +
                "SELECT\n" +
                "'' AS NAME,\n" +
                "'' AS IDENTITYTYPE,\n" +
                "'' AS IDENTITYNUMBER,\n" +
                "'' AS GENDER,\n" +
                "'' AS DUTY,\n" +
                "'' AS TEL,\n" +
                "'' AS ISMAIN,\n" +
                "'' AS REMARK \n" +
                "FROM\n" +
                hz1 +
                "WHERE \n" +
                "PK_CUSTOMER = '" + PK_CUST_SUP + "'\n" +
                "UNION\n" +
                "--供应商联系人\n" +
                "SELECT\n" +
                "'' AS NAME,\n" +
                "'' AS IDENTITYTYPE,\n" +
                "'' AS IDENTITYNUMBER,\n" +
                "'' AS GENDER,\n" +
                "'' AS DUTY,\n" +
                "'' AS TEL,\n" +
                "'' AS ISMAIN,\n" +
                "'' AS REMARK  \n" +
                "FROM\n" +
                hz2 +
                "WHERE \n" +
                "PK_SUPPLIER = '" + PK_CUST_SUP + "'";
        return sql;
    }

    /**
     * 查询凭证类别
     *
     * @param yyztCode
     * @param ts
     * @return
     */
    @Override
    public String getVoucherType(String yyztCode, String ts) {
        String sql = "SELECT\n" +
                "O.CODE as LEDGER，\n" +
                "TO_CHAR(SYSDATE,'YYYY') AS YEAR,\n" +
                "T.CODE,\n" +
                "T.NAME,\n" +
                "T.SHORTNAME AS KEYWORD,\n" +
                "T.DESCRIPTION AS REMARK,\n" +
                "T.TS \n" +
                "FROM\n" + "" + oracleBIP + ".BD_VOUCHERTYPE" + utilService.getHZ() + " T\n" +
                "LEFT JOIN " + oracleBIP + ".ORG_ORGS" + utilService.getHZ() + " O ON T.PK_ORG = O.PK_ORG\n" +
                " WHERE\n" +
                "T.DR = 0\n" +
                "AND\n" +
                "T.ENABLESTATE = 2\n" +
                " and" +
                " O.CODE in ('" + yyztCode + "','global')\n" +
                "AND \n" +
                "T.TS >'" + ts + "'";
        return sql;
    }

    /**
     * 获取辅助核算
     *
     * @param ASSID
     * @return
     */
    @Override
    public String getGlDocFreeMapSql(String ASSID) {
        String sql = "SELECT\n" +
                "ASSID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30,'' AS F31,'' AS F32,'' AS F33,'' AS F34,'' AS F35,'' AS F36,'' AS F37,'' AS F38,'' AS F39,'' AS F40,'' AS F41,'' AS F42,'' AS F43,'' AS F44,'' AS F45,'' AS F46,'' AS F47,'' AS F48,'' AS F49,'' AS F50,'' AS F51,'' AS F52,'' AS F53,'' AS F54,'' AS F55,'' AS F56,'' AS F57,'' AS F58,'' AS F59,'' AS F60, " +
                "'' AS F61,'' AS F62,'' AS F63,'' AS F64,'' AS F65,'' AS F66,'' AS F67,'' AS F68,'' AS F69,'' AS F70,'' AS F71,'' AS F72,'' AS F73,'' AS F74,'' AS F75,'' AS F76,'' AS F77,'' AS F78,'' AS F79,'' AS F80,'' AS F81,'' AS F82,'' AS F83,'' AS F84,'' AS F85,'' AS F86,'' AS F87,'' AS F88,'' AS F89,'' AS F90 \n" +
                "FROM\n" +
                "" + oracleBIP + ".GL_DOCFREE1" + utilService.getHZ() + " free \n" +
                "WHERE\n" +
                "DR = '0' \n" +
                "AND ASSID = '" + ASSID + "' \n";
        if ("2".equals(FreeMapHZ)) {
            sql += "UNION\n" +
                    "SELECT\n" +
                    "ASSID,'' AS F1,'' AS F2,'' AS F3,'' AS F4,'' AS F5,'' AS F6,'' AS F7,'' AS F8,'' AS F9,'' AS F10,'' AS F11,'' AS F12,'' AS F13,'' AS F14,'' AS F15,'' AS F16,'' AS F17,'' AS F18,'' AS F19,'' AS F20,'' AS F21,'' AS F22,'' AS F23,'' AS F24,'' AS F25,'' AS F26,'' AS F27,'' AS F28,'' AS F29,'' AS F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40,F41,F42,F43,F44,F45,F46,F47,F48,F49,F50,F51,F52,F53,F54,F55,F56,F57,F58,F59,F60," +
                    "'' AS F61,'' AS F62,'' AS F63,'' AS F64,'' AS F65,'' AS F66,'' AS F67,'' AS F68,'' AS F69,'' AS F70,'' AS F71,'' AS F72,'' AS F73,'' AS F74,'' AS F75,'' AS F76,'' AS F77,'' AS F78,'' AS F79,'' AS F80,'' AS F81,'' AS F82,'' AS F83,'' AS F84,'' AS F85,'' AS F86,'' AS F87,'' AS F88,'' AS F89,'' AS F90 \n" +
                    "FROM\n" +
                    "" + oracleBIP + ".GL_DOCFREE2" + utilService.getHZ() + " free \n" +
                    "WHERE\n" +
                    "DR = '0' \n" +
                    "AND ASSID = '" + ASSID + "' \n";
        } else if ("3".equals(FreeMapHZ)) {
            sql += "UNION\n" +
                    "SELECT\n" +
                    "ASSID,'' AS F1,'' AS F2,'' AS F3,'' AS F4,'' AS F5,'' AS F6,'' AS F7,'' AS F8,'' AS F9,'' AS F10,'' AS F11,'' AS F12,'' AS F13,'' AS F14,'' AS F15,'' AS F16,'' AS F17,'' AS F18,'' AS F19,'' AS F20,'' AS F21,'' AS F22,'' AS F23,'' AS F24,'' AS F25,'' AS F26,'' AS F27,'' AS F28,'' AS F29,'' AS F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40,F41,F42,F43,F44,F45,F46,F47,F48,F49,F50,F51,F52,F53,F54,F55,F56,F57,F58,F59,F60," +
                    "'' AS F61,'' AS F62,'' AS F63,'' AS F64,'' AS F65,'' AS F66,'' AS F67,'' AS F68,'' AS F69,'' AS F70,'' AS F71,'' AS F72,'' AS F73,'' AS F74,'' AS F75,'' AS F76,'' AS F77,'' AS F78,'' AS F79,'' AS F80,'' AS F81,'' AS F82,'' AS F83,'' AS F84,'' AS F85,'' AS F86,'' AS F87,'' AS F88,'' AS F89,'' AS F90 \n" +
                    "FROM\n" +
                    " " + oracleBIP + ".GL_DOCFREE2" + utilService.getHZ() + " free \n" +
                    " WHERE\n" +
                    " DR = '0' \n" +
                    " AND ASSID = '" + ASSID + "' \n" +
                    "UNION \n" +
                    "SELECT \n" +
                    "ASSID,'' AS F1,'' AS F2,'' AS F3,'' AS F4,'' AS F5,'' AS F6,'' AS F7,'' AS F8,'' AS F9,'' AS F10,'' AS F11,'' AS F12,'' AS F13,'' AS F14,'' AS F15,'' AS F16,'' AS F17,'' AS F18,'' AS F19,'' AS F20,'' AS F21,'' AS F22,'' AS F23,'' AS F24,'' AS F25,'' AS F26,'' AS F27,'' AS F28,'' AS F29,'' AS F30,'' AS F31,'' AS F32,'' AS F33,'' AS F34,'' AS F35,'' AS F36,'' AS F37,'' AS F38,'' AS F39,'' AS F40,'' AS F41,'' AS F42,'' AS F43,'' AS F44,'' AS F45,'' AS F46,'' AS F47,'' AS F48,'' AS F49,'' AS F50,'' AS F51,'' AS F52,'' AS F53,'' AS F54,'' AS F55,'' AS F56,'' AS F57,'' AS F58,'' AS F59,'' AS F60," +
                    " F61,F62,F63,F64,F65,F66,F67,F68,F69,F70,F71,F72,F73,F74,F75,F76,F77,F78,F79,F80,F81,F82,F83,F84,F85,F86,F87,F88,F89,F90\n" +
                    " FROM\n" +
                    " " + oracleBIP + ".GL_DOCFREE3" + utilService.getHZ() + " free \n" +
                    "WHERE\n" +
                    "DR = '0' \n" +
                    "AND ASSID = '" + ASSID + "' \n";
        }
        return sql;
    }

    /**
     * 推送凭证条数统计
     *
     * @return
     */
    @Override
    public String getGlDetailCountSQL() {
        String sql = "SELECT count(1) AS COUNT\n" +
                "FROM\n" +
                "" + utilService.getGSYY() + ".gl_detail gl\n" +
                "WHERE\n" +
                "\t((\n" +
                "\t gl.PUSHSTATUS  = '0' and gl.DATASTAUS = '0'\n" +
                "\t) \n" +
                "\tOR ((\n" +
                "\t gl.PUSHSTATUS  = '0' and gl.DATASTAUS  = '1' \n" +
                "\t) \n" +
                "\tAND (  gl.REFERENCEID  IS NOT NULL)) \n" +
                "\tOR ((\n" +
                "\t gl.PUSHSTATUS  = '0' and gl.DATASTAUS  = '2' \n" +
                "\t) \n" +
                "\tAND (  gl.REFERENCEID  IS NOT NULL))) \n" +
                "ORDER BY\n" +
                "\t gl.YYZTCODE , gl.TS";
        return sql;
    }

    /**
     * 获取推送凭证
     *
     * @return
     */
    @Override
    public String getGlDetailDataSql() {
        String sql = "SELECT\n" +
                "\tgl.MID,\n" +
                "\tgl.ID,\n" +
                "\torg.gsztCode,\n" +
                "\tgl.ACCDOCCODE,\n" +
                "\tREPLACE(SUBSTR(ACCDOCDATE,0,10), '-', '') as ACCDOCDATE,\n" +
                "\tgl.ACCDOCTYPECODE,\n" +
                "\tgl.PERIODCODE,\n" +
                "\tgl.NUMBEROFATTCH,\n" +
                "\tgl.YEAR,\n" +
                "\tgl.MAKERCODE,\n" +
                "\tgl.OPERATORCODE,\n" +
                "\tgl.SECRETLEVEL,\n" +
                "\tgl.APPROVERCODE,\n" +
                "\tgl.AUDITORCODE,\n" +
                "\tgl.BOOKERCODE,\n" +
                "\tgl.CASHIERCODE,\n" +
                "\tgl.ACCMANAGERCODE,\n" +
                "\tgl.ISAUDIT,\n" +
                "\tgl.ISBOOK,\n" +
                "\tgl.ISVOID,\n" +
                "\tgl.ISAPPROVED,\n" +
                "\tgl.EXTERNALDOC,\n" +
                "\tgl.ISREADYONLY,\n" +
                "\tgl.CREATOR,\n" +
                "\tgl.CREATETIME,\n" +
                "\tgl.LASTMODIFIER,\n" +
                "\tgl.LASTMODIFIEDTIME,\n" +
                "\tgl.SUMMARY,\n" +
                "\tACCENTRYCODE,\n" +
                "\tgl.ACCTITLECODE,\n" +
                "\tgl.DOCENTRYAMOUNT,\n" +
                "\t(CASE WHEN gl.LENDINGDIRECTION = '借' THEN '1' ELSE '2' END) as  LENDINGDIRECTION,\n" +
                "\tgl.DEPTID,\n" +
                "\tgl.RELATEDORGID,\n" +
                "\tgl.ACCEMPLOYEEID,\n" +
                "\tgl.FOREIGNCURRENCYID,\n" +
                "\tgl.QUANTITY,\n" +
                "\tgl.UNITPRICE,\n" +
                "\tgl.FOREIGNCURRENCY,\n" +
                "\tgl.EXCHANGERATE,\n" +
                "\tgl.ANCILLARYAMOUNT,\n" +
                "\tgl.BIZDATE,\n" +
                "\tgl.BIZCODE,\n" +
                "\tgl.OPERATOR,\n" +
                "\tgl.SJ,\n" +
                "\tgl.SM,\n" +
                "\tgl.SETTLEMENT,\n" +
                "\tgl.SETTLEMENTNUMBER,\n" +
                "\tgl.APPLICATION,\n" +
                "\tgl.SETTLEMENTDATE,\n" +
                "\tgl.CASHPROJECT,\n" +
                "\tgl.SPECATEID,\n" +
                "\tgl.BILLNUMBER,\n" +
                "\tgl.DATASTAUS,\n" +
                "\tgl.ISIMPORTED,\n" +
                "\tgl.FAILREASON,\n" +
                "\tCONCAT(org.gsztCode, SYS_GUID()) as BATCHNO,\n" +
                "\tgl.REFERENCEID,\n" +
                "\tgl.LYPZID,\n" +
                "\tgl.FZCODE,\n" +
                "\tgl.TS,\n" +
                "\tgl.GSSTATUS,\n" +
                "\tgl.RETURNMSG," +
                "\tgl.ASSID," +
                "\torg.GSCODE," +
                "\tgl.YYZTCODE, \n" +
                "\torg.YYCODE, \n" +
                "\torg.YYKMTX \n" +
                "FROM\n" +
                "" + utilService.getGSYY() + ".gl_detail gl\n" +
                "left join \n" +
                "" + utilService.getGSYY() + ".yy_org_orgs org on gl.orgcode = org.yyztCode\n" +
                "WHERE \n" +
                " gl.PUSHSTATUS  = '0' AND  gl.LYPZID IN (SELECT LYPZID from " + utilService.getGSYY() + ".gs_push_100)";
        return sql;
    }

    /**
     * 凭证科目辅助余额
     *
     * @param yykmtx
     * @param acctitlecode
     * @param yycode
     * @return
     */
    @Override
    public String getGlDetailkmfzSql(String yykmtx, String acctitlecode, String yycode) {
        String sql = "select \n" +
                "org.code as orgcode,\n" +
                "d.code,\n" +
                "d.name,\n" +
                "c.code as fzcode,\n" +
                "c.name as fzname\n" +
                "from \n" +
                "" + utilService.getOracle() + ".BD_ACCASS" + utilService.getHZ() + " a\n" +
                "LEFT JOIN \n" +
                "" + utilService.getOracle() + ".bd_accasoa" + utilService.getHZ() + " b on a.PK_ACCASOA = b.PK_ACCASOA\n" +
                "left join \n" +
                "" + utilService.getOracle() + ".bd_account" + utilService.getHZ() + " d on b.pk_account = d.pk_account\n" +
                "left join \n" +
                "" + utilService.getOracle() + ".BD_ACCASSITEM" + utilService.getHZ() + " c on a.pk_entity = c.pk_accassitem\n" +
                "left join \n" +
                "" + utilService.getOracle() + ".bd_accchart" + utilService.getHZ() + " ac on a.PK_ACCCHART = ac.PK_ACCCHART\n" +
                "left join \n" +
                "" + utilService.getOracle() + ".bd_accsystem" + utilService.getHZ() + " ba ON ac.PK_accsystem = ba.pk_accsystem\n" +
                "left join \n" +
                "" + utilService.getOracle() + ".org_orgs" + utilService.getHZ() + " org on ac.pk_org = org.pk_org\n" +
                " where \n" +
                "ba.CODE = '" + yykmtx + "'\n" +
                "and\n" +
                "d.code = '" + acctitlecode + "'\n" +
                "and\n" +
                "org.code = '" + yycode + "'";
        return sql;
    }
}
